import sys
import os
#pip install PyQt5
from PyQt5.QtWidgets import QApplication, QMainWindow, QFileDialog, QVBoxLayout, QHBoxLayout, QTextEdit, QPushButton, QWidget
import pandas as pd
from openpyxl import Workbook 

class ExcelViewer(QMainWindow):
    def __init__(self):
        super().__init__()

        self.setWindowTitle("Excel Viewer")
        self.setGeometry(100, 100, 600, 400)

        layout = QVBoxLayout()

        self.text_edit = QTextEdit()
        layout.addWidget(self.text_edit)

        button_layout = QHBoxLayout()
        self.load_button = QPushButton("Load Excel File")
        self.load_button.clicked.connect(self.load_excel)
        button_layout.addWidget(self.load_button)

        layout.addLayout(button_layout)

        widget = QWidget()
        widget.setLayout(layout)
        self.setCentralWidget(widget)

    def load_excel(self):
        file_dialog = QFileDialog(self)
        file_path, _ = file_dialog.getOpenFileName(self, "Open Excel File", "", "Excel Files (*.xlsx *.xls)")
        if file_path:
            try:
                #读取名为"周KPI统计"，并从第1行（第0行的数据不要）开始读取数据
                df = pd.read_excel(file_path,sheet_name="周KPI统计",header=1)
                #接通率按照降序排名，即越高排名越高
                df['Rank1']=df['接通率（S-CSCF）'].rank(ascending=False)
                #掉话序按照升序排名，即越低排名越高
                df['Rank2']=df['掉话率（S-CSCF）'].rank()
                #EPSFB流程成功率按照降序排名，即越高排名越高
                df['Rank3']=df['EPS FB流程成功率'].rank(ascending=False)
                #把省份为山东的那一列取出来
                row=df[df['省份']=='山东']
                wb = Workbook()  
                # 获取默认的活动工作表  
                ws = wb.active  
                # 在A2、A3、A4单元格写入字符串  
                ws.cell(row=2, column=1, value='接通率（降序）')
                ws.cell(row=3, column=1, value='掉话率（升序）')
                ws.cell(row=4, column=1, value='EPS FB成功率（降序）')

                #在B2 B3 B4单元格写入相应的值
                ws.cell(row=2, column=2, value=int(row['Rank1'].iloc[0]))
                ws.cell(row=3, column=2, value=int(row['Rank2'].iloc[0]))
                ws.cell(row=4, column=2, value=int(row['Rank3'].iloc[0]))


                #将文件名赋值给date1    
                date1=file_path
                #通过两次取值得到文件名中包括的日期信息
                date2=date1[-14:]
                date2=date2[:9]
                #在B1单元格写入日期信息
                ws.cell(row=1, column=2, value=date2)

                #打印日期及3项KPI指标的名称
                ws.cell(row=6, column=1, value='日期')
                ws.cell(row=6, column=2, value='接通率（S-CSCF）')
                ws.cell(row=6, column=3, value='掉话率（S-CSCF）')
                ws.cell(row=6, column=4, value='EPS FB流程成功率')

                #添入相应的值
                ws.cell(row=7, column=1, value=date2)
                A = float(row['接通率（S-CSCF）'].iloc[0])
                #print('接通率: {:.2f}%'.format(A * 100))
                A1=('{:.2f}%'.format(A * 100))
                #print(A1)

                B = float(row['掉话率（S-CSCF）'].iloc[0])
                #print('掉话率: {:.2f}%'.format(B * 100))
                B1=('{:.2f}%'.format(B * 100))

                C = float(row['EPS FB流程成功率'].iloc[0])
                #print('成功率: {:.2f}%'.format(C * 100))
                C1=('{:.2f}%'.format(C * 100))

                ws.cell(row=7, column=1, value=date2)
                ws.cell(row=7, column=2, value=A1)
                ws.cell(row=7, column=3, value=B1)
                ws.cell(row=7, column=4, value=C1)

                wb.save('3项KPI统计.xlsx')

                self.text_edit.setPlainText(df.to_string())
            except Exception as e:
                self.text_edit.setPlainText("Error loading Excel file:\n" + str(e))

def main():
    app = QApplication(sys.argv)
    excel_viewer = ExcelViewer()
    excel_viewer.show()
    sys.exit(app.exec_())

if __name__ == "__main__":
    main()